package cn.com.bluemoon.daps.model.utils.score;


import cn.com.bluemoon.daps.common.domain.ResultBean;
import cn.com.bluemoon.daps.model.dto.DapDataModelTableDto;
import cn.com.bluemoon.daps.model.entity.DapDataModelByteLength;
import cn.com.bluemoon.daps.model.entity.DapDataModelSubordinateKey;
import cn.com.bluemoon.daps.model.entity.DapDataModelTableField;
import cn.com.bluemoon.daps.model.entity.DapDataModelTableScoreRule;
import com.alibaba.druid.util.MySqlUtils;
import org.apache.commons.lang.StringUtils;

import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class TableStructureScore {

    private static final String UNIQUE = "unq_";
    private static final String NORMAL = "idx_";
    private static final List<String> LIST = Arrays.asList("tinyint", "int", "smallint", "bigint", "decimal", "mediumint");
    private static final List<String> TEXT_LIST = Arrays.asList("TINYTEXT", "TEXT", "MEDIUMTEXT", "LONGTEXT");
    private static final List<String> STRING_LIST = Arrays.asList("VARCHAR", "CHAR", "TINYBLOB", "BLOB", "MEDIUMBLOB", "LONGBLOB");
    private static final List<String> CHAR_LIST = Arrays.asList("VARCHAR", "CHAR", "TINYBLOB", "TINYTEXT", "TEXT", "MEDIUMTEXT", "LONGTEXT", "BLOB", "MEDIUMBLOB", "LONGBLOB");
    private static final List<String> CHAR_OR_VARCHAR = Arrays.asList("VARCHAR", "CHAR");

    private static final Pattern NUMBER = Pattern.compile("^[0-9]*$");

    /**
     * 评分列表
     *
     * @param ruleList
     * @param tableInfo 表、字段、索引、分区信息
     */
    public static ResultBean<Double> getTableScore(List<DapDataModelTableScoreRule> ruleList,
                                                   DapDataModelTableDto tableInfo, List<DapDataModelByteLength> lengthList) {

        double score = 100.0;
        if (null == tableInfo || tableInfo.getFieldList().isEmpty()) {
            return ResultBean.ok(0.0);
        }
        for (DapDataModelTableScoreRule rule : ruleList) {
            score = scoreRule(rule, tableInfo, score, lengthList);
        }
        return ResultBean.ok(score);
    }

    /**
     * 单表评分
     *
     * @param ruleList
     * @param tableInfo
     * @return
     */
    public static List<DapDataModelTableScoreRule> singleTable(List<DapDataModelTableScoreRule> ruleList,
                                                               DapDataModelTableDto tableInfo, List<DapDataModelByteLength> lengthList) {

        double score = 100.0;
        for (DapDataModelTableScoreRule rule : ruleList) {
            score = scoreRule(rule, tableInfo, score, lengthList);
        }
        return ruleList;
    }

    /**
     * 评分规则
     *
     * @param rule
     * @param tableInfo
     * @param score
     * @return
     */
    public static double scoreRule(DapDataModelTableScoreRule rule, DapDataModelTableDto tableInfo,
                                   double score, List<DapDataModelByteLength> lengthList) {

        switch (rule.getRuleCode()) {
            case STANDARD_PERCENT:
                score = fieldUseStandardScore(rule, tableInfo.getFieldList(), score);
                break;
            case EXIST_PRIMARY:
                score = havePrimaryKey(rule, tableInfo.getFieldList(), score);
                break;
            case PRIMARY_IS_INT:
                score = havePrimaryKeyIsInteger(rule, tableInfo.getFieldList(), score);
                break;
            case AUTO_IS_UNSIGNED:
                score = haveAutoIncrementIsUnSign(rule, tableInfo.getFieldList(), score);
                break;
            case IS_TIMESTAMP:
                score = haveTimestamp(rule, tableInfo.getFieldList(), score);
                break;
            case CHAR_IS_NULL_NUMBER:
                score = haveNotNull(rule, tableInfo.getFieldList(), score);
                break;
            case CHAR_LENGTH:
                score = charLength(rule, tableInfo.getFieldList(), score);
                break;
            case CHAR_PERCENT:
                score = charPercent(rule, tableInfo.getFieldList(), score);
                break;
            case SINGLE_MAX_LENGTH:
                score = singleMax(rule, tableInfo.getFieldList(), score, lengthList);
                break;
            case INDEX_NUMBER:
                score = indexNum(rule, tableInfo.getKeyList(), score);
                break;
            case INDEX_PREFIX_LEGAL:
                score = indexPrefix(rule, tableInfo.getKeyList(), score);
                break;
            case IS_EXIST_KEY_WORDS:
                score = haveKeywords(rule, tableInfo.getFieldList(), score);
                break;
            case IS_USE_TEXT:
                score = haveUseText(rule, tableInfo.getFieldList(), score);
                break;
            case NOT_TINYINT:
                score = haveNotTinyint(rule, tableInfo.getFieldList(), score);
                break;
            case TABLE_FIELD_TOTAL:
                score = fieldTotal(rule, tableInfo.getFieldList(), score);
                break;
            default:
                break;
        }
        return score;
    }

    /**
     * 引用标准评分
     *
     * @param rule
     * @param fields
     * @param score
     * @return
     */
    public static double fieldUseStandardScore(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields, double score) {

        double size = fields.stream().filter(f -> !f.getStandardId().equals("0")).count();
        rule.setScore(size >= Double.valueOf(rule.getScoreThreshold()) ? rule.getScoreWeight() : 0);
        return size >= Double.valueOf(rule.getScoreThreshold()) ? score : score - rule.getScoreWeight();
    }

    /**
     * 是否有主键
     *
     * @param rule
     * @param fields
     * @param score
     * @return
     */
    public static double havePrimaryKey(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields, double score) {

        // 判断是否存在主键
        List<DapDataModelTableField> collect = fields.stream()
                .filter(f -> f.getPrimaryKey() == 1).collect(Collectors.toList());
        rule.setScore(collect.isEmpty() ? 0 : rule.getScoreWeight());
        return collect.isEmpty() ? score - rule.getScoreWeight() : score;
    }

    /**
     * 判断主键类型
     *
     * @param rule
     * @param fields
     * @param score
     * @return
     */
    public static double havePrimaryKeyIsInteger(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields, double score) {

        // 判断是否存在主键
        List<DapDataModelTableField> collect = fields.stream()
                .filter(f -> f.getPrimaryKey() == 1).collect(Collectors.toList());
        // 有主键的情况下去筛选主键字段类型
        if (!collect.isEmpty()) {
            List<DapDataModelTableField> primaryKeyList = collect.stream()
                    .filter(f -> LIST.contains(f.getDataType())).collect(Collectors.toList());
            rule.setScore(primaryKeyList.isEmpty() ? 0 : rule.getScoreWeight());
            return primaryKeyList.isEmpty() ? score - rule.getScoreWeight() : score;
        } else {
            rule.setScore(rule.getScoreWeight());
        }
        return score;
    }

    /**
     * 为自增列
     *
     * @param fields
     * @param score
     * @return
     */
    public static double haveAutoIncrementIsUnSign(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields, double score) {

        // 是否为自增列
        List<Integer> autoList = fields.stream().filter(f -> f.getAutoIncrement() == 1).map(DapDataModelTableField::getUnSigned).collect(Collectors.toList());
        // 判断是否为无符号
        if (!autoList.isEmpty()) {
            rule.setScore(autoList.contains(1) ? rule.getScoreWeight() : 0);
            return autoList.contains(1) ? score : score - rule.getScoreWeight();
        } else {
            rule.setScore(rule.getScoreWeight());
        }
        // 无自增列，不扣分
        return score;
    }

    /**
     * 时间类型是否为timestamp类型
     *
     * @param fields
     * @param score
     * @return
     */
    public static double haveTimestamp(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields, double score) {

        long count = fields.stream().filter(f -> f.getDataType().equalsIgnoreCase("datetime")).count();
        rule.setScore(count == 0 ? rule.getScoreWeight() : count <= 2 ? (double) (rule.getScoreWeight()) / 2 : 0);
        return count == 0 ? score : count <= 2 ? score - ((double) (rule.getScoreWeight()) / 2) : score - rule.getScoreWeight();
    }

    /**
     * 默认值为空的个数
     *
     * @param fields
     * @param score
     * @return
     */
    public static double haveNotNull(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields, double score) {

        // 查询字符类型为空的数量
        long nullNumber = fields.stream().filter(f -> STRING_LIST.contains(f.getDataType().toUpperCase()) && f.getNotNull() == 0).count();
        // 小于5个不扣分
        long over = nullNumber - Long.parseLong(rule.getScoreThreshold());
        rule.setScore(over <= 0 ? rule.getScoreWeight() : over <= Long.parseLong(rule.getScoreThreshold()) ? rule.getScoreWeight() - over : 0);
        return over <= 0 ? score : over <= Long.parseLong(rule.getScoreThreshold()) ? score - over : score - rule.getScoreWeight();
    }

    /**
     * 字符串类型长度超过256个数
     *
     * @param fields
     * @param score
     * @return
     */
    public static double charLength(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields, double score) {

        // 判断是否全部为数值类型，正则表达式
        List<String> dataTypeList = fields.stream().filter(f -> STRING_LIST.contains(f.getDataType().toUpperCase()))
                .map(DapDataModelTableField::getLength).collect(Collectors.toList());
        // 过滤掉不是数值类型的数据，过滤掉长度为空的数据
        long lengthNum = dataTypeList.stream().filter(t -> StringUtils.isNotBlank(t) && NUMBER.matcher(t).matches()
                && Integer.parseInt(t) > 256).count();
        long over = lengthNum - Long.parseLong(rule.getScoreThreshold());
        rule.setScore(over <= 0 ? rule.getScoreWeight() : over <= Double.valueOf(rule.getScoreWeight()) ? rule.getScoreWeight() - over : 0);
        return over <= 0 ? score : over <= Double.valueOf(rule.getScoreWeight()) ? score - over : score - rule.getScoreWeight();
    }

    /**
     * 字符串百分比
     *
     * @param fields
     * @param score
     * @return
     */
    public static double charPercent(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields, double score) {

        // 字符串类型的数量
        long count = fields.stream().filter(f -> CHAR_LIST.contains(f.getDataType().toUpperCase())).count();
        // 占字段总数的百分比
        double fieldPercent = count / (double) fields.size();
        if (fieldPercent <= Double.valueOf(rule.getScoreThreshold())) {
            rule.setScore(rule.getScoreWeight());
            return score;
        } else {
            // 超出的个数
            long over = count - (int) (fields.size() * Double.valueOf(rule.getScoreThreshold()));
            rule.setScore(over <= rule.getScoreWeight() ? rule.getScoreWeight() - over : 0);
            return over <= rule.getScoreWeight() ? score - over : score - rule.getScoreWeight();
        }
    }

    /**
     * 单行数据最大长度
     *
     * @param rule
     * @param fields
     * @param score
     * @return
     */
    public static double singleMax(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields,
                                   double score, List<DapDataModelByteLength> lengthList) {

        // 获得char和varchar的数据类型的个数
        double count = fields.stream().filter(f -> CHAR_OR_VARCHAR.contains(f.getDataType().toUpperCase())).count();
        // 剔除长度为空和不是数值类型数据
        double sum = fields.stream().filter(f -> CHAR_OR_VARCHAR.contains(f.getDataType().toUpperCase())
                && StringUtils.isNotBlank(f.getLength()) && NUMBER.matcher(f.getLength()).matches())
                .map(DapDataModelTableField::getLength).mapToDouble(Double::valueOf).sum();
        double charLength = count * sum * 4;
        List<String> dataTypeList = fields.stream().filter(f -> !CHAR_OR_VARCHAR.contains(f.getDataType().toUpperCase()))
                .map(DapDataModelTableField::getDataType).collect(Collectors.toList());
        // 计算数据类型出现的次数
        Map<String, Long> map = dataTypeList.stream().collect(Collectors.groupingBy(p -> p, Collectors.counting()));
        // 计算总字节长度
        for (Map.Entry<String, Long> entry : map.entrySet()) {
            List<DapDataModelByteLength> list = lengthList.stream().filter(l -> l.getDataType().equalsIgnoreCase(entry.getKey())).collect(Collectors.toList());
            if (!list.isEmpty()) {
                charLength += list.stream().findFirst().get().getByteNum() * entry.getValue();
            }
        }
        // 判断总长度
        rule.setScore(charLength <= Double.valueOf(rule.getScoreThreshold()) ? rule.getScoreWeight() : charLength <= 2048 ? (double) (rule.getScoreWeight()) / 2 : 0);
        return charLength <= Double.valueOf(rule.getScoreThreshold()) ? score : charLength <= 2048 ? score - (double) (rule.getScoreWeight()) / 2
                : score - rule.getScoreWeight();
    }

    /**
     * 表索引个数超过5个进行扣分
     *
     * @param keys
     * @param score
     * @return
     */
    public static double indexNum(DapDataModelTableScoreRule rule, List<DapDataModelSubordinateKey> keys, double score) {

        double over = keys.size() - Double.valueOf(rule.getScoreThreshold());
        rule.setScore(over <= 0 ? rule.getScoreWeight() : over <= Double.valueOf(rule.getScoreThreshold()) ? rule.getScoreWeight() - over : 0);
        return over <= 0 ? score : over <= Double.valueOf(rule.getScoreThreshold()) ? score - over : score - rule.getScoreWeight();
    }

    /**
     * 索引前缀是否为idx_ unq_
     *
     * @param keys
     * @param score
     * @return
     */
    public static double indexPrefix(DapDataModelTableScoreRule rule, List<DapDataModelSubordinateKey> keys, double score) {

        // 索引为空
        if (keys.isEmpty()) {
            rule.setScore(rule.getScoreWeight());
            return score;
        }
        // 为normal的索引
        List<DapDataModelSubordinateKey> normalList = keys.stream().filter(k -> k.getKeyType() == 2).collect(Collectors.toList());
        // 为unique的索引
        List<DapDataModelSubordinateKey> uniqueList = keys.stream().filter(f -> f.getKeyType() == 3).collect(Collectors.toList());
        // 前缀不是为idx_的个数
        long notNorNum = normalList.stream().filter(k -> k.getKeyName().length() < NORMAL.length()
                || (k.getKeyName().length() >= NORMAL.length() && !k.getKeyName().substring(0, NORMAL.length())
                .equalsIgnoreCase(NORMAL))).count();
        // 前缀不是unq_的个数
        long notUnqNum = uniqueList.stream().filter(k -> k.getKeyName().length() < UNIQUE.length()
                || (k.getKeyName().length() >= UNIQUE.length() && !k.getKeyName().substring(0, UNIQUE.length())
                .equalsIgnoreCase(UNIQUE))).count();
        long total = notNorNum + notUnqNum;
        rule.setScore(total <= rule.getScoreWeight() ? rule.getScoreWeight() - total : 0);
        return total <= rule.getScoreWeight() ? score - total : score - rule.getScoreWeight();
    }

    /**
     * 是否存在关键字
     *
     * @param rule
     * @param fields
     * @param score
     * @return
     */
    public static double haveKeywords(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields, double score) {

        // 字段名存在关键字的个数
        long count = fields.stream().filter(f -> MySqlUtils.isKeyword(f.getFieldName())).count();
        rule.setScore(count <= rule.getScoreWeight() ? rule.getScoreWeight() - count : 0);
        return count <= rule.getScoreWeight() ? score - count : score - rule.getScoreWeight();
    }

    /**
     * 是否使用*text数据类型
     *
     * @param rule
     * @param fields
     * @param score
     * @return
     */
    public static double haveUseText(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields, double score) {

        // 查询数据类型为text
        double count = fields.stream().filter(f -> TEXT_LIST.contains(f.getDataType().toUpperCase())).count();
        rule.setScore(count < 2 ? (double) rule.getScoreWeight() - count * Double.valueOf(rule.getScoreWeight()) * 0.5 : 0);
        return count < 2 ? score - count * Double.valueOf(rule.getScoreWeight()) * 0.5 : score - rule.getScoreWeight();

    }

    /**
     * 类型、状态字段非tinyint数据类型
     *
     * @param rule
     * @param fields
     * @param score
     * @return
     */
    public static double haveNotTinyint(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields, double score) {
        rule.setScore(rule.getScoreWeight());
        return score;
    }

    /**
     * 单表字段总数
     *
     * @param rule
     * @param fields
     * @param score
     * @return
     */
    public static double fieldTotal(DapDataModelTableScoreRule rule, List<DapDataModelTableField> fields, double score) {

        rule.setScore(fields.size() <= Double.valueOf(rule.getScoreThreshold()) ? rule.getScoreWeight() : fields.size() <= 60
                ? (double) rule.getScoreWeight() * 0.5 : 0);
        return fields.size() <= Double.valueOf(rule.getScoreThreshold()) ? score : fields.size() <= 60
                ? score - (double) rule.getScoreWeight() * 0.5 : score - rule.getScoreWeight();

    }
}
